{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Easy\n",
    "\n",
    "The \"Neeps\" database includes details of all teaching events in the School of Computing at Napier University in Semester 1 of the 2000/2001 academic year.\n",
    "\n",
    "Graduated questions\n",
    "\n",
    "The timetable database contains the following tables:\n",
    "\n",
    "- ut_staff\n",
    "\n",
    "id | name\n",
    "---|-----\n",
    "co.ACg | Cumming, Andrew\n",
    "co.ACr | Crerar, Dr Alison\n",
    "co.AFA | Armitage, Dr Alistair\n",
    " | ... Result truncated.\n",
    "\n",
    "The staff table simply gives the unique identifier for each member of staff together with their full name.\n",
    "\n",
    "- ut_student\n",
    "\n",
    "id | name | sze | parent\n",
    "---|------|----:|----------\n",
    "co1.BAe | BAe | 15 | \n",
    "co1.CO | Computing 1st Year | 150 | \n",
    "co1.CO.a | Computing 1st Year a | 20 | co1.CO\n",
    "co1.CO.c | Computing 1st Year c | 20 | co1.CO\n",
    "co1.CO.d | Computing 1st Year d | 20 | co1.CO\n",
    " | | | ... Result truncated.\n",
    "\n",
    "Each entry in the student table represents a group of students who follow the same timetable. The parent field is used to maintain a hierachy of groups. A student in group co1.CO.a is also in group co1.CO\n",
    "\n",
    "- ut_room\n",
    "\n",
    "id | name | capacity | parent\n",
    "---|------|---------:|---------\n",
    "co.117+118 |  | 32 | \n",
    "co.117 |  | 16 | co.117+118\n",
    "co.118 |  | 16 | co.117+118\n",
    "co.B7 |  | 40 | \n",
    " | | | ... result truncated\n",
    "\n",
    "All classrooms are listed in the room table. The parent relation is used to maintain groupings. The row co.117+118 represents a pair of rooms - sometimes these rooms are used for a single event.\n",
    "\n",
    "- ut_event\n",
    "\n",
    "id | modle | kind | dow | tod | duration | room\n",
    "---|-------|------|-----|-----|---------:|----------\n",
    "co12004.L01 | co12004 | L | Wednesday | 11:00 | 1 | cr.SMH\n",
    "co12004.L02 | co12004 | L | Monday | 17:00 | 1 | cr.B13\n",
    "co12004.T01 | co12004 | T | Monday | 11:00 | 2 | co.G78+G82\n",
    "co12004.T02 | co12004 | T | Tuesday | 15:00 | 2 | co.B7\n",
    "co12004.T03 | co12004 | T | Tuesday | 13:00 | 2 | co.G78+G82\n",
    " | | | | | | ... Result truncated.\n",
    "\n",
    "Every event is associated with a single modle (deliberately mis-spelled version of module - which is a reserved word in one of engines). An event occurs on a particular day of the week (dow) at a particular time of day (tod). Every event has a duration specified as a whole number of hours.\n",
    "\n",
    "- ut_attends\n",
    "\n",
    "student | event\n",
    "--------|------\n",
    "co1.BAe | co12004.L01\n",
    "co1.CO | co12004.L01\n",
    "co1.IS | co12004.L01\n",
    "co1.SE.pt | co12004.L02\n",
    "co1.CO.a | co12004.T01\n",
    " | ... Result truncated.\n",
    "\n",
    "The attends table links event to student. It realises the many to many relation.\n",
    "\n",
    "- ut_teaches\n",
    "\n",
    "staff | event\n",
    "------|----------\n",
    "co.AMn | co12004.L01\n",
    "co.RK | co12004.L01\n",
    "co.SRM | co12004.L01\n",
    "co.RK | co12004.L02\n",
    "co.SRM | co12004.L02\n",
    " | ... Result truncated.\n",
    "\n",
    "The teaches table links event to staff.\n",
    "\n",
    "- ut_occurs\n",
    "\n",
    "event | week\n",
    "------|-------:\n",
    "co12004.L01 | 01\n",
    "co12004.L01 | 02\n",
    "co12004.L01 | 03\n",
    "co12004.L01 | 04\n",
    "co12004.L01 | 05\n",
    " | ... Result truncated.\n",
    "\n",
    "Most events occur on weeks 01 to 13. This table records exactly which weeks are part of the plan for each event.\n",
    "\n",
    "- ut_modle\n",
    "\n",
    "id | name\n",
    "---|---------\n",
    "co12001 | Rapid Application Development\n",
    "co12002 | Software Development 1A\n",
    "co12003 | Professional Skills\n",
    "co12004 | Applications Workshop\n",
    " | ... Result truncated.\n",
    "\n",
    "By the 21st century, the Gradgrindians had completed their assimilation of Education. The process of \"modularisation\" was just one of their crimes against learning.\n",
    "\n",
    "- ut_week\n",
    "\n",
    "id | wkstart\n",
    "--:|-------:\n",
    "01 | 02/10/00\n",
    "02 | 09/10/00\n",
    "03 | 16/10/00\n",
    " | ...Result truncated\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 60)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ut_staff = pd.read_sql_table('ut_staff', engine)\n",
    "ut_student = pd.read_sql_table('ut_student', engine)\n",
    "ut_event = pd.read_sql_table('ut_event', engine)\n",
    "ut_room = pd.read_sql_table('ut_room', engine)\n",
    "ut_attends = pd.read_sql_table('ut_attends', engine)\n",
    "ut_teaches = pd.read_sql_table('ut_teaches', engine)\n",
    "ut_occurs = pd.read_sql_table('ut_occurs', engine)\n",
    "ut_modle = pd.read_sql_table('ut_modle', engine)\n",
    "ut_week = pd.read_sql_table('ut_week', engine)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the room id in which the event co42010.L01 takes place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>cr.132</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       room\n",
       "102  cr.132"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ut_event.loc[ut_event['id']=='co42010.L01', ['room']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**For each event in module co72010 show the day, the time and the place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dow</th>\n",
       "      <th>tod</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>14:00</td>\n",
       "      <td>cr.SMH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>09:00</td>\n",
       "      <td>cr.B8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>09:00</td>\n",
       "      <td>co.B7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>12:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Tuesday</td>\n",
       "      <td>11:00</td>\n",
       "      <td>co.G75+G76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>16:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Thursday</td>\n",
       "      <td>10:00</td>\n",
       "      <td>co.LB42+LB46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Wednesday</td>\n",
       "      <td>13:00</td>\n",
       "      <td>co.117+118</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dow    tod          room\n",
       "133  Wednesday  14:00        cr.SMH\n",
       "134    Tuesday  09:00         cr.B8\n",
       "135  Wednesday  09:00         co.B7\n",
       "136    Tuesday  12:00  co.LB42+LB46\n",
       "137    Tuesday  11:00    co.G75+G76\n",
       "138  Wednesday  16:00  co.LB42+LB46\n",
       "139   Thursday  10:00  co.LB42+LB46\n",
       "140  Wednesday  13:00    co.117+118"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ut_event.loc[ut_event['modle']=='co72010', ['dow', 'tod', 'room']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of the staff who teach on module co72010.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Cumming, Andrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Chisholm, Ken</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name\n",
       "29   Cumming, Andrew\n",
       "118    Chisholm, Ken"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (ut_event.merge(ut_teaches, left_on='id', right_on='event')\n",
    "     .merge(ut_staff, left_on='staff', right_on='id'))\n",
    "a.loc[a['modle']=='co72010', ['name']].drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**Give a list of the staff and module number associated with events using room cr.132 on Wednesday, include the time each event starts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>modle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Murray, Jim</td>\n",
       "      <td>co22009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Varey, Alison</td>\n",
       "      <td>co32021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name    modle\n",
       "149    Murray, Jim  co22009\n",
       "191  Varey, Alison  co32021"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (ut_event.merge(ut_teaches, left_on='id', right_on='event')\n",
    "     .merge(ut_staff, left_on='staff', right_on='id'))\n",
    "a.loc[(a['room']=='cr.132') & (a['dow']=='Wednesday'), ['name', 'modle']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Give a list of the student groups which take modules with the word 'Database' in the name.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>BSc4 Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>170</th>\n",
       "      <td>BEng4 Network and Distributing Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>PgD Information Systems</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>PgD Software Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>PgD Software Technology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>PgD Information Systems a (HCI)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>PgD Information Systems b (DS)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>PgD Information Systems d (BT)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>PgD Information Systems e (OOP)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>PgD Information System pt. Tues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>PgD Software Technology pt. Tues</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>PgD IT and e-Commerce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>PgD IT and e-Commerce eve sem 3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     name_x\n",
       "169                          BSc4 Computing\n",
       "170  BEng4 Network and Distributing Systems\n",
       "249                 PgD Information Systems\n",
       "250                                    None\n",
       "251                PgD Software Engineering\n",
       "252                 PgD Software Technology\n",
       "253         PgD Information Systems a (HCI)\n",
       "254          PgD Information Systems b (DS)\n",
       "255          PgD Information Systems d (BT)\n",
       "257         PgD Information Systems e (OOP)\n",
       "258         PgD Information System pt. Tues\n",
       "259        PgD Software Technology pt. Tues\n",
       "278                   PgD IT and e-Commerce\n",
       "280         PgD IT and e-Commerce eve sem 3"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (ut_student.merge(ut_attends, left_on='id', right_on='student')\n",
    "     .merge(ut_event, left_on='event', right_on='id')\n",
    "     .merge(ut_modle, left_on='modle', right_on='id'))\n",
    "(a.loc[a['name_y'].str.lower().str.contains('database'), ['name_x']]\n",
    " .drop_duplicates())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
